﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Secams.utils;
using System.Data.OleDb;
using ADODB;
using System.Data;
using Secams.model;

namespace Secams.controller
{
    class EventTraceDao
    {
        public const string TABLENAME = "datEventTrace";
        public string _dbFilepath;
        
        public EventTraceDao(string dbFilepath)
        {
            if (dbFilepath != null)
                _dbFilepath = dbFilepath;
            else
                _dbFilepath = "../../data/Secams.mdb";
        }

        public bool initialize() {
            bool retval = false;
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {

                if (!MdbUtil.existsTable(dbConn, TABLENAME))
                {
                    //http://www.sulc.edu/sulcalumni/app/lib/pear/docs/MDB/doc/datatypes.html#5.1.3
                    OleDbCommand cmd = dbConn.CreateCommand();
                    cmd.CommandText = "CREATE TABLE "+TABLENAME+" (participantId TEXT(32)"
                        + ", scenarioOrder TEXT(64)"
                        + ", scenarioVersion TEXT(64)"
                        + ", trialNum INT" // trial number
                        + ", eventType TEXT(24) null" // trial number
                        + ", eventTime DATETIME null" // trial number
                        + ", destAddr TEXT(64) null" // increment per trial
                        + ", port INT null" // Ex: Left Button
                        + ", path TEXT(64) null" // Ex: Pressed
                        + ", operation TEXT(64) null" // Left Button Pressed
                        + ", isThreath BIT" // 1 = is threath BIT or LOGICAL or YESNO
                        + ", decision YESNO" // 1 = decided as threath
                        + ", remark TEXT(125) null );";
                    cmd.ExecuteNonQuery();
                    retval = true;
                }
                dbConn.Close();
            }
            return retval;
        }

        public void insert(TraceEvent trace)
        {
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {
                OleDbCommand insertCommand = new OleDbCommand(
                    "INSERT INTO " + TABLENAME + " (participantId, scenarioOrder, scenarioVersion, trialNum, eventType, eventTime, destAddr, port, path, operation, isThreath, decision, remark)"
                    + " values (@participantId, @scenarioOrder, @scenarioVersion, @trialNum, @eventType, @eventTime, @destAddr, @port, @path, @operation, @isThreath, @decision, @remark)", dbConn);

                insertCommand.Parameters.Add("@participantId", trace.participantId);
                insertCommand.Parameters.Add("@scenarioOrder", trace.scenarioOrder);
                insertCommand.Parameters.Add("@scenarioVersion", trace.scenarioVersion);
                insertCommand.Parameters.Add("@trialNum", trace.trialNum);
                insertCommand.Parameters.Add("@eventType", trace.eventProfile.niEvent.type);
                insertCommand.Parameters.Add("@eventTime", trace.eventProfile.niEvent.timestamp.ToOADate());
                insertCommand.Parameters.Add("@destAddr", trace.eventProfile.niEvent.destAddr);
                insertCommand.Parameters.Add("@port", trace.eventProfile.niEvent.port);
                insertCommand.Parameters.Add("@path", trace.eventProfile.niEvent.path);
                insertCommand.Parameters.Add("@operation", trace.eventProfile.niEvent.operation);
                insertCommand.Parameters.Add("@isThreath", OleDbType.Boolean);
                insertCommand.Parameters["@isThreath"].Value = trace.eventProfile.niEvent.isThreat;
                insertCommand.Parameters.Add("@decision", trace.eventProfile.labeledAsThreath );
                insertCommand.Parameters.Add("@remark", trace.eventProfile.remark);

                insertCommand.ExecuteNonQuery();
            }
        }
    }
}
